The present invention relates generally to the field of information processing by digital computers and, more particularly, to the processing and presentation of information by application programs, particularly electronic spreadsheets.
Before computers, numerical analyses, particularly financial ones, were usually prepared on an accountant's columnar pad or spreadsheet, with pencil and calculator in hand. By organizing data into columns and rows, spreadsheets afford the rapid assimilation of information by a reader. The task of preparing a spreadsheet on paper, however, is not quite so fast. Instead, the process tends to be very slow, as each entry must be tediously calculated and entered into the spreadsheet. Since all calculations are the responsibility of the preparer, manually prepared spreadsheets are also prone to error. Hence, preparation of spreadsheets by hand is slow, tedious, and unreliable.
With the advent of microcomputers, a solution was forthcoming in the form of "electronic spreadsheets." Better known simply as "spreadsheets," these software programs provide a computerized replacement for the traditional financial modeling tools: the accountant's columnar pad, pencil, and calculator. In some regards, spreadsheet programs are to those tools what wordprocessors are to typewriters. Spreadsheets offer dramatic improvements in ease of creating, editing, and using financial models.
A typical spreadsheet program configures the memory of a computer to resemble the column/row or grid format of an accountant's columnar pad, thus providing a visual calculator for a user. Because this "pad" exists dynamically in the computer's memory, however, it differs from paper pads in several important ways. Locations in the electronic spreadsheet, for example, must be communicated to the computer in a format which it can understand. A common scheme for accomplishing this is to assign a number to each row in a spreadsheet, and a letter to each column. To reference a location at column A and row 1 (i.e., the upper-left hand corner), for example, the user types in "A1". In this manner, the spreadsheet defines an addressable storage location or "cell" at each intersection of a row with a column.
Data entry into an electronic spreadsheet occurs in much the same manner that information would be entered on an accountant's pad. After a screen cursor is positioned at a desired location, the user can enter alphanumeric information. Besides holding text and numeric information, however, spreadsheet cells can store special instructions or "formulas" specifying calculations to be performed on the data stored in spreadsheet cells. In this fashion, cell references can serve as variables in an equation, thereby allowing precise mathematical relationships to be defined between cells.
A particular advantage of electronic spreadsheets is the ability to create a multitude of "what if" scenarios from a single data model. This ability stems largely from the spreadsheet formulas, which are the means by which a user tells an electronic spreadsheet system how to manipulate and analyze one's data. After a set of mathematical relationships has been entered into a worksheet, the spread of information can be "recalculated" using different sets of assumptions, with the results of each recalculation appearing relatively quick. Performing this operation manually, with paper and pencil, would require recalculating every relationship in the model with each change made. Expectedly, electronic spreadsheets have quickly displaced their pencil-and-paper counterparts for modeling user information.
The structure and operation of a spreadsheet program, including spreadsheet formulas and "macros," are documented in the technical, trade, and patent literature. For an overview, see e.g., Cobb, D., Using 1-2-3, Que Corp., 1985; and Campbell, M., Quattro Pro 4.0 Handbook, 4th Ed., 1992. The disclosures of each of the foregoing references are hereby incorporated by reference.
Spreadsheet formulas are fundamental to the creation and operation of a spreadsheet data model. During creation of a particular spreadsheet or worksheet model, a user enters formulas in worksheet cells the same way he or she enters values and labels. Typically, a formula begins with a number or with a special character (e.g., +, -, (, @, ., #, or $) to distinguish the entry from raw data. More particularly, formulas are constructed from one or more of four basic components: operators, values, cell references, and @-functions. Each of these will be briefly reviewed.
Operators, which indicate how user data are to be combined and manipulated, typically include well-known operators such as -, +, *, /, and the like. Values are the information (e.g., numeric, logical, or text) which formulas are to act upon. To include a value in a formula, the user simply types it as he or she desires it to appear.
Cell references, on the other hand, allow a user to employ a value in one's current formula that is derived from another cell. For instance, if a cell having an address of B3 contains the value of 10, the formula 5+B3 placed in any other cell of the spreadsheet will evaluate to the result of 15. A referenced cell containing no value (blank) or a label is, depending on implementation, treated as zero or as an error (ERR). Much of the flexibility of electronic spreadsheets stems from this ability to establish relationships via cell references.
The @-functions are built-in formulas provided for frequently-used calculations. Some @-functions perform a calculation on a single value, such as @SQRT(n) which calculates the square root of the number n. Other @-functions perform calculations on cell ranges, such as @SUM(B3..B10) which sums the values stored in cells B3 to B10. @-functions can be used in formulas where one might use a value or cell reference.
Before meaningful results of a given spreadsheet may be presented to a user, the formulas of a given spreadsheet must first be evaluated or recalculated. "Recalculation" is the process by which a spreadsheet's cells, particularly formula-storing ones, are evaluated to express values. Different recalculation orders are well known including, for instance, natural, rowwise, and columnwise. Each will be reviewed in turn.
Natural recalculation entails evaluating the least dependent cells first. In other words, the spreadsheet system first recalculates those cells upon which the formulas and functions in other cells depend. Suppose, for example, that one desires to create a business forecast (data model) with the following assumptions: (1) Cost of goods is 30% of revenue, (2) Advertising expenses are $5,000, and (3) Taxes are 28% of pre-tax profit. This may be modeled in a spreadsheet model having the following cell entries:
______________________________________ Cell Formula Comment ______________________________________ B3 0.3 * B2 Cost of goods - - calculated as 30% of B2 (Revenue stored in B2) B4 5000 Advertising expense B5 + B2 - B3 - B4 Pre-tax profit B6 0.28 * B5 Taxes due - - calculated as 28% of B5 (Pre-tax profit) B7 + B5 - B6 Net profit is B5 (pre-tax profit) less B6 (taxes) ______________________________________
Now, the user may quickly forecast a net profit (value expressed in cell B7) for a given revenue value (entered into cell B2). A multitude of revenue values may be forecasted by simply entering different values into B2.
Interdependencies between the cells dictate how a spreadsheet system actually performs recalculation. Before a value for cell B7 may be calculated, for instance, the value for cell B5 (and for cell B6) must be known. Before B5 can be calculated, a value for cell B3 (and cells B2 and B4) must be known. Cell B2 must be evaluated before a value for B3 can be expressed. Because natural recalculation recognizes these interdependencies, it is a recalculation method commonly employed in conventional electronic spreadsheets.
Rowwise recalculation, in contrast, moves across each row from the left-most cell to the right-most cell, beginning with the top-most row in the model and working to the bottom-most. Columnwise recalculation moves from the top-most cell to the bottom-most cell in a column, starting with the left-most column and moving to the right-most. Since the rowwise and columnwise recalculation methods typically do not recognize the interdependencies between cell values, multiple recalculation passes may be required to produce a recalculated spreadsheet.
Regardless of which recalculation order is employed, the quickness or "speed" with which recalculation can be completed is often used as an indicator of the overall performance of an electronic spreadsheet. A slow recalculation time for a spreadsheet system ensures that the user will waste time waiting for the system to complete its recalculation. Quick recalculation, on the other hand, allows the user to complete his or her task with minimum interruption. Accordingly, electronic spreadsheets which perform recalculations quickly enjoy greater commercial success.
With the present trend of employing electronic spreadsheets to model increasingly complex data models, the ability of present-day electronic spreadsheet systems to quickly evaluate models has become taxed. For instance, it is not uncommon for a financial model of even modest complexity to have a recalculation time on the order of several minutes. While improved computer hardware, including more efficient floating-point processors and faster clock speeds, has helped, many users of electronic spreadsheets still spend a significant portion of their time waiting idly while their spreadsheets are being recalculated. Accordingly, there is much interest in improving the speed with which electronic spreadsheets perform recalculation of spreadsheets.